package day01;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Set;

public class TestUpdate {
    @Test
    public void updateTom() throws SQLException, ClassNotFoundException {
        Connection conn = JDBCutils.getConnection();

        String sql = "UPDATE emp SET sal = 8848 WHERE empno = 700";

        PreparedStatement ps = conn.prepareStatement(sql);

        int rows = ps.executeUpdate();

        System.out.println(rows > 0 ? "添加成功" : "添加失败");

        ps.close();
        conn.close();
    }

    @Test
    public void deleteJerry() throws SQLException, ClassNotFoundException {
        Connection connection = JDBCutils.getConnection();
        String sql = "DELETE FROM emp WHERE empno = 800";
        int rows = connection.prepareStatement(sql).executeUpdate();

        System.out.println(rows > 0 ? "执行成功" : "执行失败");

        connection.close();
    }

    @Test
    public void selectData() throws SQLException, ClassNotFoundException {
        Connection connection = JDBCutils.getConnection();
        String sql = "SELECT empno,ename,job FROM emp";
        PreparedStatement ps = connection.prepareCall(sql);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {

            int empno = rs.getInt("empno");
            String ename = rs.getString("ename");
            String job = rs.getString("job");
            System.out.println(empno + "----" + ename + "----" + job);
        }

        rs.close();
        ps.close();
        connection.close();

    }

    @Test
    public void TestSelect2() throws ClassNotFoundException, SQLException {
        Connection connection = JDBCutils.getConnection();

        int id = 700;
        String name = "'OR'1'='1";
        String sql = "SELECT empno,ename,job FROM emp WHERE empno='+id+'AND ename='"+name+"'";

        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            int empno = rs.getInt("empno");
            String ename = rs.getString("ename");
            String job = rs.getString("job");
            System.out.println(empno+"\t"+ename+"\t"+job);
        }
        rs.close();
        ps.close();
        connection.close();
    }

    @Test
    public void TestSelectTrue()throws SQLException,ClassNotFoundException{
        Connection connection = JDBCutils.getConnection();
        int id = 700;
        String name = "Tom";
        String Fname = "'OR'1'='1";
        String sql = "SELECT empno,ename,job FROM emp WHERE empno=? AND ename=?";
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setInt(1,id);
        ps.setString(2,name);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            int empno = rs.getInt("empno");
            String ename = rs.getString("ename");
            String job = rs.getString("job");
            System.out.println(empno+"\t"+ename+"\t"+job);
        }
        rs.close();
        ps.close();
        connection.close();
    }
}
